package DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import Model.Anwesenheit;


public class Anwesenheit_DAO {

	private Lernbuero_DAO lernbueroDAO=new Lernbuero_DAO();
	private Schueler_DAO schuelerDAO=new Schueler_DAO();

	public Anwesenheit get(int id)
	{
		Anwesenheit a=null;
		Connection conn=Allgemein_DAO.getConnection();
		PreparedStatement statement=null;
		ResultSet rs=null;
		String sql="select * from anwesenheit where anwesenheitid=?";
		try {
			statement = conn.prepareStatement(sql);
			statement.setInt(1, id);
			rs=statement.executeQuery();

			if (rs.next())
			{
				a=new Anwesenheit();
				a.setId(id);
				a.setKw(rs.getInt("kw"));
				a.setSchuljahr(rs.getInt("aschuljahr"));
				a.setSchueler(schuelerDAO.get(rs.getInt("schuelerID")));
				a.setLernbuero(lernbueroDAO.getLernbuero(rs.getInt("lbid")));
				a.setWochentag(rs.getInt("wochentag"));
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				rs.close();
				statement.close();
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return a;
	}

	public void update(Anwesenheit a)
	{
		Connection conn=Allgemein_DAO.getConnection();
		String sql="update anwesenheit set aschuljahr=?, kw=?, wochentag=?, schuelerID=?, lbid=? where anwesenheitid=?";
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, a.getSchuljahr());
			statement.setInt(2, a.getKw());
			statement.setInt(3, a.getWochentag());
			statement.setInt(4, a.getSchueler().getID());
			statement.setInt(5, a.getLernbuero().getID());
			statement.setInt(6, a.getId());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void anlegen(Anwesenheit a)
	{
		Connection conn=Allgemein_DAO.getConnection();
		String sql="insert into Anwesenheit VALUES (?, ?, ?, ?, ?, ?)";
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, a.getId());
			statement.setInt(2, a.getSchuljahr());
			statement.setInt(3, a.getKw());
			statement.setInt(4, a.getWochentag());
			statement.setInt(5, a.getSchueler().getID());
			statement.setInt(6, a.getLernbuero().getID());

			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

	public void delete(int delete[])
	{
		//delte[0] = kw, delte[1] = jahr, delet[2] = lbid
		Connection conn=Allgemein_DAO.getConnection();
		String sql="delete from anwesenheit where kw = ? and aschuljahr = ? and lbid = ?";
		try {
			PreparedStatement statement = conn.prepareStatement(sql);
			statement.setInt(1, delete[0]);
			statement.setInt(2, delete[1]);
			statement.setInt(3, delete[2]);
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   
		finally {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}

}
